
package net.cc.jsy.service;

import com.baomidou.mybatisplus.core.MybatisConfiguration;
import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import lombok.extern.slf4j.Slf4j;
import net.cc.jsy.entity.TableEntity;
import net.cc.jsy.mapper.MdbDbMapper;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.datasource.pooled.PooledDataSource;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;
import org.springframework.stereotype.Service;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;
import java.util.concurrent.CopyOnWriteArrayList;

/**
 * 数据库管理 服务层实现
 *
 * @author jsy
 * @date 2020-09-17
 */
@Slf4j
@Service
public class MdbDbInfoService {
    private static final String JDBC_URL = "jdbc:ucanaccess://%s;openExclusive=false;ignoreCase";

    /**
     * 创建一个数据库链接
     * @param address
     * @return
     * @throws IOException
     */
    private SqlSessionFactory getSqlSessionFactory(String address) throws IOException {
        PooledDataSource dataSource = new PooledDataSource();
        dataSource.setDriver("net.ucanaccess.jdbc.UcanaccessDriver");
        dataSource.setUrl(String.format(JDBC_URL, address));
        dataSource.setUsername("");
        dataSource.setPassword("");
        ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        Resource[] resources = resolver.getResources("classpath:/mappings/db/MdbDbMapper.xml");
        MybatisSqlSessionFactoryBean sqlSessionFactoryBean = new MybatisSqlSessionFactoryBean();
        sqlSessionFactoryBean.setMapperLocations(resources);
        sqlSessionFactoryBean.setDataSource(dataSource);
        sqlSessionFactoryBean.setConfiguration(new MybatisConfiguration());
        try {
            return sqlSessionFactoryBean.getObject();
        } catch (Exception e) {
            throw new RuntimeException("数据库连接失败,请检查配置", e);
        }
    }

    /**
     * 获取一个表的所有数据
     *
     * @param path
     * @return
     * @author jingshiyu
     * @date 2020.9.21 16:22
     */
    public List<Map<String, Object>> getOneTableDetail(String path, String tableName) throws Exception {
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory(path);
        SqlSession sqlsession = sqlSessionFactory.openSession();
        List<Map<String, Object>> list = getAllTableDetail(tableName, sqlsession);
        sqlsession.close();
        return list;
    }

    /**
     * 获取数据库的所有表
     *
     * @param path
     * @return
     * @author jingshiyu
     * @date 2020.9.21 16:21
     */
    public List<TableEntity> getAllTable(String path) throws Exception {
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory(path);
        SqlSession sqlsession = sqlSessionFactory.openSession();
        List<TableEntity> list = getAllTableBySession(sqlsession);
        //若是传入的session,就不要关闭了
        sqlsession.close();
        return list;
    }

    public List<TableEntity> getAllTableBySession(SqlSession sqlsession) throws Exception {
        MdbDbMapper mapper = sqlsession.getMapper(MdbDbMapper.class);
        List<TableEntity> list = mapper.getAllTable();
        //获取/创建对应的表名
        //取出所有的原name
        List<String> nameList = new CopyOnWriteArrayList<>();
        list.parallelStream().forEach(one -> nameList.add(one.getName()));
        return list;
    }


    /**
     * 获取一个数据库表的所有数据
     *
     * @param path
     * @return
     * @author jingshiyu
     * @date 2020.9.21 16:22  <表名,表数据>
     */
    public Map<String, List<Map<String, Object>>> getAllTableAllDetail(String path) throws Exception {
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory(path);
        SqlSession sqlsession = sqlSessionFactory.openSession();
        List<TableEntity> tableList = getAllTableBySession(sqlsession);
        Map<String, List<Map<String, Object>>> tableDataMap = new ConcurrentHashMap<>();
        if (CollectionUtils.isNotEmpty(tableList)) {
            for (TableEntity tableEntity : tableList) {
                List<Map<String, Object>> list = getAllTableDetail(tableEntity.getName(), sqlsession);
                if (CollectionUtils.isNotEmpty(list)) {
                    tableDataMap.put(tableEntity.getName(), list);
                }
            }
        }

        sqlsession.close();
        return tableDataMap;
    }

    /**
     * 获取一个表中所有的数据
     * @param tableName
     * @param sqlsession
     * @return
     * @throws Exception 返回的是一个表的所有的数据
     */
    public List<Map<String, Object>> getAllTableDetail(String tableName, SqlSession sqlsession) throws Exception {
        MdbDbMapper mapper = sqlsession.getMapper(MdbDbMapper.class);
        try {
            return mapper.getTableDetail(tableName);
        } catch (Exception e) {
            return new ArrayList<>();
        }
    }

    /**
     * 读取一个目录下的所有的mdb的数据
     * @param path
     * @param data
     * @return
     * @throws Exception
     */
    public Map<String, Object> getAllMdbData(String path, Map<String, Object> data, String tableName) throws Exception {
        if (data == null) {
            data = new ConcurrentHashMap<>();
        }
        File file = new File(path);
        if (!file.exists()) {
            return data;
        }
        if (file.isDirectory()) {
            //mulu
            File[] files = file.listFiles();
            for (File file1 : files) {
                data = getAllMdbData(file1.getAbsolutePath(), data, tableName);
            }
        } else {
            if (path.endsWith("mdb")) {
                //数据库文件
                Map<String, List<Map<String, Object>>> allTableData;
                if (StringUtils.isNotBlank(tableName)) {
                    allTableData = new ConcurrentHashMap<>();
                    List<Map<String, Object>> oneTableDetail = getOneTableDetail(path, tableName);
                    if (CollectionUtils.isNotEmpty(oneTableDetail)) {
                        allTableData.put(tableName, oneTableDetail);
                    }
                } else {
                    allTableData = getAllTableAllDetail(path);
                }
                if (allTableData.size() > 0) {
                    data.put(file.getName().replace(".mdb", ""), allTableData);
                }
            }
        }
        return data;
    }


    public void exportDate(String path, String outPath, String tableName) throws Exception {
        //获取所有数据
        Map<String, Object> allMdbData = getAllMdbData(path, null, tableName);
        //导出
        if (allMdbData != null && allMdbData.size() > 0) {
            for (String name : allMdbData.keySet()) {
                Map<String, List<Map<String, Object>>> tableDataMap = (Map<String, List<Map<String, Object>>>) allMdbData.get(name);
                if (tableDataMap != null && tableDataMap.size() > 0) {
                    for (String oneTableName : tableDataMap.keySet()) {
                        List<Map<String, Object>> dataList = tableDataMap.get(oneTableName);
                        XSSFWorkbook workbook = new XSSFWorkbook();
                        XSSFSheet sheet = workbook.createSheet();
                        workbook.setActiveSheet(0);
                        int rowi = 0;
                        //设置表头
                        List<String> keyList = new ArrayList<>(dataList.get(0).keySet());
                        int ci = 0;
                        XSSFRow row = sheet.createRow(rowi);
                        for (int i = 0; i < keyList.size(); i++) {
                            String param = keyList.get(i);
                            row.createCell(i).setCellValue(param);
                        }
                        for (int j = 1; j < dataList.size(); j++) {
                            ++rowi;
                            row = sheet.createRow(rowi);
                            Map<String, Object> dataMap = dataList.get(j);
                            for (int i = 0; i < keyList.size(); i++) {
                                Object value = dataMap.get(keyList.get(i));
                                if (value != null) {
                                    row.createCell(i).setCellValue(value.toString());
                                }
                            }
                        }
                        //导出
                        exportLocal(workbook, name + "_" + oneTableName, outPath);
                    }
                }

            }
        }
    }

    public static void exportLocal(Workbook wb, String fileName, String path) throws Exception {
        File file = new File(path);
        file.mkdirs();
        FileOutputStream fileOut = new FileOutputStream(path + "\\" + fileName + ".xlsx");
        wb.write(fileOut);
        fileOut.close();
    }
}
